﻿CREATE PROCEDURE admin.s_Notes_Fix
	@VIN varchar(17) = NULL
AS
--DECLARE @VIN varchar(17) SET @VIN = '3C8FY78G13T599607'
IF @VIN IS NULL BEGIN --[
DECLARE @SQL varchar(max)
SELECT @SQL = ISNULL(@SQL,'')+'EXEC admin.s_Notes_Fix '''+CI.VIN+'''
'
FROM         t_CarInfo AS CI INNER JOIN
(	SELECT DISTINCT t_Loan.ID
	FROM         t_Loan INNER JOIN
												t_Note ON t_Loan.ID = t_Note.LoanID
	WHERE     (t_Loan.StatusUser = 'NATASHA')
) AS L ON CI.LoanID = L.ID AND CI.VIN > ''
GROUP BY CI.VIN
HAVING      (COUNT(*) > 1)

EXEC (@SQL)
RETURN
END --]

PRINT 'Doing '+@VIN
BEGIN TRAN
EXEC sp__Context_SecurityOff 1

--SELECT LAST.LoanID,N.*
UPDATE T_Note SET LoanID = LAST.LoanID
FROM t_CarInfo CI
INNER JOIN t_Note N
	ON CI.LoanID = N.LoanID
INNER JOIN (SELECT MAX(LoanID)LoanID FROM t_CarInfo WHERE VIN = @VIN)LAST
	ON N.LoanID <> LAST.LoanID
WHERE CI.VIN = @VIN
--ORDER BY NoteDate DESC


EXEC sp__Context_SecurityOff 0
COMMIT